package org.jgs1904.apachepoidemo.poi;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Objects;

/**
 * @author: 默苍璃
 * @date: 2024-03-05 09:55
 */
public class ExcelFillBlankCells {
    public static void main(String[] args) {
        try {
            FileInputStream file = new FileInputStream("D:\\work\\work\\task\\同步云星空\\生产云星空客户_2024031809123463_1138509.xlsx");
            Workbook workbook = new XSSFWorkbook(file);
            Sheet sheet = workbook.getSheetAt(0);

            for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) {
                Row row = sheet.getRow(rownum);
                if (Objects.isNull(row)) {
                    row = sheet.createRow(rownum);
                }
                Cell cell = row.getCell(0);
                if (Objects.isNull(cell)) {
                    cell = row.createCell(0);
                }
                if (StringUtils.isEmpty(cell.getStringCellValue())) {
                    Cell prev = sheet.getRow(rownum - 1).getCell(0);
                    cell.setCellValue(prev.getStringCellValue());
                }

                Cell cell1 = row.getCell(1);
                if (Objects.isNull(cell1)) {
                    cell1 = row.createCell(1);
                }
                if (StringUtils.isEmpty(cell1.getStringCellValue())) {
                    Cell prev = sheet.getRow(rownum - 1).getCell(1);
                    cell1.setCellValue(prev.getStringCellValue());
                }
                Cell cell2 = row.getCell(2);
                if (Objects.isNull(cell2)) {
                    cell2 = row.createCell(2);
                }
                if (StringUtils.isEmpty(cell2.getStringCellValue())) {
                    Cell prev = sheet.getRow(rownum - 1).getCell(2);
                    cell2.setCellValue(prev.getStringCellValue());
                }
                // if (cell != null && cell.getCellType() == CellType.BLANK) {
                //     for (int i = rownum - 1; i >= 0; i--) {
                //         Row prevRow = sheet.getRow(i);
                //         if (prevRow != null) {
                //             Cell prevCell = prevRow.getCell(0);
                //             if (prevCell != null && prevCell.getCellType() != CellType.BLANK) {
                //                 cell.setCellValue(prevCell.getNumericCellValue());
                //                 break;
                //             }
                //         }
                //     }
                // }

            }

            FileOutputStream outputStream = new FileOutputStream("D:\\temp\\生产云星空客户_2024031809123463_1138509.xlsx");
            workbook.write(outputStream);
            workbook.close();
            file.close();
            outputStream.close();

            System.out.println("Excel file processed successfully!");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
